package com.LGF.Book.dao;

import com.LGF.Book.bean.BookType;
import com.LGF.Book.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BookTypeDao {

    QueryRunner runner = new QueryRunner();

    /**
     * 根据图书名称获取图书类型信息
     * @param tname
     * @return
     * @throws SQLException
     */
    public BookType getByTname(String tname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from booktype where tname=?";
        BookType bookType = runner.query(conn, sql, new BeanHandler<BookType>(BookType.class), tname);
        DBHelper.close(conn);
        return bookType;

    }



    /**
     * 根据图书类型名模糊查询图书类型信息
     * @param tname
     * @return
     * @throws SQLException
     */
    public List<BookType> selectTName(String tname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from booktype where tname like '%' ? '%' ";
        List<BookType> bookTypes = runner.query(conn, sql, new BeanListHandler<BookType>(BookType.class),tname);
        DBHelper.close(conn);
        return bookTypes;
    }



    /**
     * 分页查询图书类型信息
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<BookType> getByPage(int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from booktype limit ?,? ";
        List<BookType> bookTypes = runner.query(conn, sql, new BeanListHandler<BookType>(BookType.class),page,limit);
        DBHelper.close(conn);
        return bookTypes;
    }

    /**
     * 获取所有图书类型
     * @return
     * @throws SQLException
     */
    public List<BookType> getAll() throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from booktype ";
        List<BookType> bookTypes = runner.query(conn, sql, new BeanListHandler<BookType>(BookType.class));
        DBHelper.close(conn);
        return bookTypes;
    }

    /**
     * 根据id查询图书类型信息
     * @param id
     * @return
     * @throws SQLException
     */
    public BookType getById(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from booktype where id=? ";
        BookType bookTypes = runner.query(conn, sql,new BeanHandler<BookType>(BookType.class),id);
        DBHelper.close(conn);
        return bookTypes;
    }

    /**
     * 添加图书类型
     * @param tname
     * @return
     * @throws SQLException
     */
    public int add(String tname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="insert into booktype(tname) values(?)";
        int count = runner.update(conn, sql, tname);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据id修改图书类型
     * @param id
     * @param tname
     * @return
     */
    public int modify(long id,String tname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="update booktype set tname=? where id=?";
        int count = runner.update(conn, sql, tname,id);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据ID删除图书类型
     * @param id
     * @return
     * @throws SQLException
     */
    public int remove(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="delete from booktype where id=?";
        int count = runner.update(conn, sql, id);
        DBHelper.close(conn);
        return count;
    }


}
